#!/usr/bin/env python3

import sys
import os
import cx_Oracle
import hashlib
import pdb
import logging
import click
import logging

ora_username='eular_dev'
ora_password='Paas0426'
ora_conn_str='192.168.100.113:1521/nlpass01'
#ora_conn_str='127.0.0.1:1521/nlpass01'


@click.group()
def main():
    pass

# One chunk is a list of tables
# returns a list of chunks
def get_chunks(table_list, input_file, parallel):
    tablist = []
    if table_list:
        tablist = table_list.split(',')
    elif input_file:
        with open(input_file, 'r', encoding="utf-8") as f:
            while True:
                tabname = f.readline().strip()
                if len(tabname) > 0:
                    tablist.append(tabname)
                else:
                    break;
    else:
        raise Exception('Error: No input tables.')

    chunk_size = int(len(tablist) / parallel)
    if chunk_size <= 0:
        raise Exception('Error: table number: {}, parallel: {}.'.format(len(tablist), parallel))

    chunked_list = [tablist[i:i+chunk_size] for i in range(0, len(tablist), chunk_size)]
    return chunked_list

@main.command('tabdef', help='Get defination of each table.', no_args_is_help=True)
@click.option('--input-file', '-i', help='Intput file')
@click.option('--tab-list', '-l', help='Input table list e.g. -l "tb1,tb2,tb3"')
def cmp_table(input_file, tab_list):
    chunks = get_chunks(tab_list, input_file, 1)
    ora_conn = cx_Oracle.connect(ora_username, ora_password, ora_conn_str)
    ora_cur = ora_conn.cursor()
    for tablename in chunks[0]:
        print('{}:'.format(tablename.upper()))
        ora_cur.execute("""SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = :tab ORDER BY COLUMN_ID""", tab=tablename.upper())
        ora_res = ora_cur.fetchall()
        for i in range(len(ora_res)):
            print('    {} {} {} {}'.format(ora_res[i][0], ora_res[i][1], ora_res[i][2], ora_res[i][3]))

@main.command('tab', help='Get all table names')
def get_table():
    ora_conn = cx_Oracle.connect(ora_username, ora_password, ora_conn_str)
    ora_cur = ora_conn.cursor()
    ora_cur.execute("""SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME""")
    ora_res = ora_cur.fetchall()
    for ora_row in ora_res: 
        print('{}'.format(ora_row[0].upper()))


@main.command('seq', help='Get all sequence names')
def get_seq():
    ora_conn = cx_Oracle.connect(ora_username, ora_password, ora_conn_str)
    ora_cur = ora_conn.cursor()
    ora_cur.execute("""SELECT SEQUENCE_NAME FROM USER_SEQUENCES ORDER BY SEQUENCE_NAME""")
    ora_res = ora_cur.fetchall()
    for ora_row in ora_res:
        print('{}'.format(ora_row[0].upper()))


@main.command('index', help='Get primary key, index and unique defination of each table.', no_args_is_help=True)
@click.option('--input-file', '-i', help='Intput file')
@click.option('--tab-list', '-l', help='Input table list e.g. -l "t1,t2,t3"')
def get_index(input_file, tab_list):
    chunks = get_chunks(tab_list, input_file, 1)
    ora_conn = cx_Oracle.connect(ora_username, ora_password, ora_conn_str)
    ora_cur = ora_conn.cursor()
    for tablename in chunks[0]:
        # ora index cols in define order
        ora_cur.execute("""SELECT INDEX_NAME, LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_POSITION) MYCOLS
                           FROM USER_IND_COLUMNS WHERE TABLE_NAME = :tab GROUP BY INDEX_NAME""", 
                           tab=tablename.upper())
        ora_res = ora_cur.fetchall()
        ora_index_list = []
        for ora_row in ora_res:
            ora_index_list.append(ora_row[1].upper())
        ora_index_list.sort()
        for it in ora_index_list:
            print('{} : {}'.format(tablename.upper(), it))
        #print('{} : {}'.format(tablename.upper(), ' - '.join(ora_index_list)))


@main.command('fk', help='Get foreign key defination of each table.', no_args_is_help=True)
@click.option('--input-file', '-i', help='Intput file')
@click.option('--tab-list', '-l', help='Input table list e.g. -l "t1,t2,t3"')
def get_fk(input_file, tab_list):
    chunks = get_chunks(tab_list, input_file, 1)
    ora_conn = cx_Oracle.connect(ora_username, ora_password, ora_conn_str)
    ora_cur = ora_conn.cursor()
    for tablename in chunks[0]:
        ora_cur.execute("""SELECT A.TABLE_NAME,
                                  (SELECT LISTAGG(COLUMN_NAME, ',' ) WITHIN GROUP (ORDER BY POSITION) 
                                      FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = A.CONSTRAINT_NAME) FK_COLUMNS,
                                  B.TABLE_NAME PK_TABLE_NAME,
                                  (SELECT LISTAGG(COLUMN_NAME, ',' ) WITHIN GROUP (ORDER BY POSITION) 
                                      FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = A.R_CONSTRAINT_NAME) PK_COLUMNS
                           FROM USER_CONSTRAINTS A,USER_CONSTRAINTS B 
                           WHERE A.R_CONSTRAINT_NAME = B.CONSTRAINT_NAME AND 
                                 A.CONSTRAINT_TYPE = 'R' AND 
                                 A.TABLE_NAME = :tab
                           ORDER BY FK_COLUMNS, PK_TABLE_NAME, PK_COLUMNS""", tab=tablename.upper())
        # 自己的表名 自己表中的列 引用外键表的表名 外键表中的列
        ora_fk_list = []
        ora_res = ora_cur.fetchall()
        for row in ora_res:
            fkcols = []
            for col in row:
                fkcols.append(col)
            ora_fk_list.append('{} : {} -> {} : {}'.format(fkcols[0],fkcols[1],fkcols[2],fkcols[3]))
        ora_fk_list.sort()
        for it in ora_fk_list:
            print('{}'.format(it))

@main.command('check', help='Get check constraint info of each table.', no_args_is_help=True)
@click.option('--input-file', '-i', help='Intput file')
@click.option('--tab-list', '-l', help='Input table list e.g. -l "tb1,tb2,tb3"')
def cmp_table(input_file, tab_list):
    chunks = get_chunks(tab_list, input_file, 1)
    ora_conn = cx_Oracle.connect(ora_username, ora_password, ora_conn_str)
    ora_cur = ora_conn.cursor()
    for tablename in chunks[0]:
        print('{}:'.format(tablename.upper()))
        ora_cur.execute("""SELECT SEARCH_CONDITION_VC FROM USER_CONSTRAINTS WHERE TABLE_NAME = :tab AND CONSTRAINT_TYPE='C' ORDER BY SEARCH_CONDITION_VC""", tab=tablename.upper())
        ora_res = ora_cur.fetchall()
        for i in range(len(ora_res)):
            print('    {}'.format(ora_res[i][0]))


if __name__ == '__main__':
    main(None)


